package com.coderman.daoclient.connections;

import com.coderman.daoclient.annotations.Table;
import com.coderman.daoclient.bean.KVPair;
import com.coderman.daoclient.enums.FieldTypeEnum;
import com.coderman.daoclient.exceptions.IdNullException;
import com.coderman.daoclient.reflect.BeanMapInfo;
import com.coderman.daoclient.reflect.FieldInfo;
import com.coderman.daoclient.utils.ClassUtils;
import com.coderman.daoclient.utils.JdbcUtils;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * Created by ${fanchunshuai} on 2017-7-27.
 *
 * @author fanchunshuai
 * @version: V1.0
 * @Desc:
 */
@Component
@Service
public class StatementTask implements AbstractStatementTask {

    @Autowired
    private HikariDataSource hikariDataSource;


    private final ThreadLocal<Connection> threadLocal = new ThreadLocal();


    private Logger logger = LoggerFactory.getLogger(this.getClass());

    public StatementTask(){
        try {
            BeanMapInfo.initBean();
        } catch (Exception e) {
            logger.error("init bean error",e);
        }
    }


    @Override
    public PreparedStatement createPreparedStatement(String exeSql) throws SQLException {
        PreparedStatement preparedStatement =  hikariDataSource.getConnection().prepareStatement(exeSql);
        return preparedStatement;
    }

    @Override
    public PreparedStatement createPreparedStatement(String exeSql,int generateOrder) throws SQLException {
        PreparedStatement preparedStatement =  hikariDataSource.getConnection().prepareStatement(exeSql,generateOrder);
        return preparedStatement;
    }


    @Override
    public Long exeInsert(Object o) throws Exception {
        List<FieldInfo> fieldInfoList = ClassUtils.getBean().get(o.getClass());
        StringBuffer buffer = new StringBuffer("INSERT INTO ");
        Table table = o.getClass().getAnnotation(Table.class);
        if(table == null){
            throw  new NullPointerException("entity not have annotation of Table:"+o.getClass().getName());
        }
        buffer.append(table.name());

        StringBuffer columnBuff = new StringBuffer(" (");

        StringBuffer valueBuff = new StringBuffer(" VALUES (");

        for (FieldInfo fieldInfo : fieldInfoList){
            Method method = o.getClass().getMethod(fieldInfo.getGetMethodName());
            Object object = method.invoke(o);
            if(!Objects.isNull(object) && fieldInfo.getIsID() == 0){
                columnBuff.append(fieldInfo.getColName()+",");
                valueBuff.append("?,");
                fieldInfo.setFieldValue(object);
            }
        }

        String cv = columnBuff.substring(0,columnBuff.length() - 1)+") " + valueBuff.substring(0,valueBuff.length() - 1)+")" ;
        buffer.append(cv);
        logger.info("insertSql = "+buffer.toString());
        PreparedStatement statement = this.createPreparedStatement(buffer.toString(),Statement.RETURN_GENERATED_KEYS);
        try {
            realExeUpdateSql(fieldInfoList,statement);
        }catch (Exception e){
            e.printStackTrace();
            logger.error("insert bean error ",e);
            return -1L;
        }
        ResultSet rs = statement.getGeneratedKeys();
        long id = 0;
        if (rs.next()){
            id = rs.getLong(1);
        }
        JdbcUtils.closeStatement(statement);
        return id;
    }

    @Override
    public int exeUpdate(Object o) throws Exception {
        List<FieldInfo> fieldInfoList = ClassUtils.getBean().get(o.getClass());
        StringBuffer buffer = new StringBuffer("UPDATE  ");
        Table table = o.getClass().getAnnotation(Table.class);
        if(Objects.isNull(table) || "".equals(table.name())){
            throw  new NullPointerException("entity not have annotation of Table:"+o.getClass().getName());
        }
        buffer.append(table.name() + " SET ");
        String whereId = "";
        for (FieldInfo fieldInfo : fieldInfoList){

            Method method = o.getClass().getMethod(fieldInfo.getGetMethodName());
            Object object = method.invoke(o);

            if(!Objects.isNull(object) && fieldInfo.getIsID() == 0){
                fieldInfo.setFieldValue(object);
                buffer.append(fieldInfo.getColName()+"=?,");
            }
            //id处理
            if(fieldInfo.getIsID() == 1){
                if(Objects.isNull(object)){
                    logger.error("id value is null",new NullPointerException());
                    throw new IdNullException("id value is null ",new NullPointerException());
                }
                whereId = " WHERE " + fieldInfo.getColName() + "=" + object.toString();
            }
        }
        String sql = buffer.substring(0,buffer.length() - 1);
        buffer = new StringBuffer(sql);
        buffer.append(whereId);
        logger.info("updateSql : "+buffer.toString());
        PreparedStatement statement = this.createPreparedStatement(buffer.toString());
        int x =  realExeUpdateSql(fieldInfoList,statement);
        JdbcUtils.closeStatement(statement);
        return x;
    }


    @Override
    public Map<String, String> getMap(Class clazzBean, String keyColumn, String valueColumn) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazzBean);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazzBean.getClass().getName());
        }
        StringBuffer buffer = new StringBuffer("SELECT ");
        buffer.append(keyColumn);
        buffer.append(","+valueColumn);
        buffer.append(" FROM ");
        buffer.append(tableName);
        PreparedStatement statement = this.createPreparedStatement(buffer.toString());
        Map<String,String> map = new HashMap<>();
        ResultSet set = statement.executeQuery();
        while (set.next()){
            String key = set.getObject(1).toString();
            if(set.getObject(2) != null){
                String value = set.getObject(2).toString();
                map.put(key,value);
            }else {
                map.put(key,"");
            }

        }

        return map;
    }

    @Override
    public List getList(Class clazzBean, String sql) throws Exception {
        System.out.println("sql = "+sql);
        PreparedStatement statement = this.createPreparedStatement(sql);
        return    realExeQueryList(clazzBean,statement);
    }

    @Override
    public List getList(Class clazz, List list) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }

        List<FieldInfo> fieldInfoList = BeanMapInfo.getFieldMap().get(clazz);
        StringBuffer buffer = new StringBuffer("SELECT  ");

        for (FieldInfo fieldInfo : fieldInfoList){
            buffer.append(fieldInfo.getColName()+" ,");
        }

        buffer = new StringBuffer(buffer.substring(0,buffer.length() - 1));
        buffer.append(" FROM ");

        buffer.append(tableName);

        if( !Objects.isNull(list) && !list.isEmpty()){
            buffer.append(" WHERE 1=1  ");
            for (int i = 0;i < list.size();i++){
                KVPair kvPair = (KVPair) list.get(i);
                buffer.append(" AND "+ kvPair.getKey()+"=?  ");
            }

            PreparedStatement statement = this.createPreparedStatement(buffer.toString());

            for (int i = 0;i < list.size();i++){
                KVPair kvPair = (KVPair) list.get(i);
                convertParamValue(kvPair.getValue(),(i + 1),statement);
            }
            return realExeQueryList(clazz,statement,fieldInfoList);
        }
        logger.info("selectSql : "+buffer.toString());
        PreparedStatement statement = this.createPreparedStatement(buffer.toString());
        return realExeQueryList(clazz,statement,fieldInfoList);
    }

    @Override
    public Long exeQueryCount(Class clazz, List list) throws Exception {
        if(list == null || list.isEmpty()){
            return exeQueryCount(clazz);
        }
        StringBuffer buffer = new StringBuffer("SELECT COUNT(1) FROM ");

        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }
        buffer.append(tableName);
        //有性能问题
        buffer.append(" WHERE 1=1  ");
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            buffer.append(" AND "+ kvPair.getKey()+"=?  ");
        }
        PreparedStatement statement = this.createPreparedStatement(buffer.toString());

        logger.info("selectSql : "+buffer.toString());
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            convertParamValue(kvPair.getValue(),(i + 1),statement);
        }
        Long count =  realExeQuery(Long.class,statement,null);
        return count;
    }

    @Override
    public Long exeQueryCount(Class clazz) throws Exception {
        StringBuffer buffer = new StringBuffer("SELECT COUNT(1) FROM ");

        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }
        buffer.append(tableName);
        PreparedStatement statement = this.createPreparedStatement(buffer.toString());
        Long count =  realExeQuery(Long.class,statement,null);
        return count;
    }

    @Override
    public Object exeQueryTID(Class clazz, Object id) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazz);

        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }

        List<FieldInfo> fieldInfoList = BeanMapInfo.getFieldMap().get(clazz);
        StringBuffer buffer = new StringBuffer("SELECT  ");

        for (FieldInfo fieldInfo : fieldInfoList){
            buffer.append(fieldInfo.getColName()+" ,");
        }

        buffer = new StringBuffer(buffer.substring(0,buffer.length() - 1));

        buffer.append(" FROM ");
        buffer.append(tableName);
        buffer.append(" WHERE ");
        buffer.append(" id = ? limit 1");
        logger.info("selectSql : "+buffer.toString());

        PreparedStatement statement = this.createPreparedStatement(buffer.toString());
        statement.setObject(1,id);
        return realExeQuery(clazz,statement,fieldInfoList);

    }


    private <T> T realExeQuery(Class<?> clazz,PreparedStatement statement,List<FieldInfo> fieldInfoList) throws Exception {
        ResultSet set = statement.executeQuery();

        if(fieldInfoList ==null && set.next()){
            return (T) set.getObject(1);
        }

        T t = (T) clazz.getConstructor(new Class[] {}).newInstance();
        if(set.next()){
            for (FieldInfo fieldInfo : fieldInfoList){
                Method setMethod = clazz.getMethod(fieldInfo.getSetMethodName(),new Class[] {FieldTypeEnum.getType(fieldInfo.getColType())});
                setMethod.invoke(t, new Object[] { set.getObject(fieldInfo.getColName())});// 调用对象的setXXX方法
            }
        }
        return t;
    }

    private <T> List<T> realExeQueryList(Class<T> clazz,PreparedStatement statement) throws Exception {
        List<T> list = new ArrayList<T>();
        Field [] fields  = clazz.getDeclaredFields();
        ResultSet rs = statement.executeQuery();
        Map<String,String> map = new HashMap<>();
        int count = rs.getMetaData().getColumnCount();

        for (int i = 0;i< count;i++){
            map.put(rs.getMetaData().getColumnName(i+1),rs.getMetaData().getColumnName(i+1));
        }

        List<FieldInfo> fieldInfoList = BeanMapInfo.getFieldMap().get(clazz);

        // 循环读取结果集的每一行的每一列
        while (rs.next()){
            // 获得无参构造对象方法创建实例
            T t = (T) clazz.getConstructor(new Class[] {}).newInstance();
            for (FieldInfo field : fieldInfoList) {
                String colName = field.getColName();

                if(Objects.isNull(map.get(colName))){
                    continue;
                }

                Object value = getResultSetValue(rs,field.getColType(),field.getColName());

                // 获得属性的首字母并转换为大写，与setXXX对应
                String firstLetter = field.getFieldName().substring(0, 1).toUpperCase();

                String setMethodName = "set" + firstLetter
                        + field.getFieldName().substring(1);
                Method setMethod = clazz.getMethod(setMethodName,
                        new Class[] { FieldTypeEnum.getType(field.getColType()) });
                setMethod.invoke(t, new Object[] { value });// 调用对象的setXXX方法
            }
            list.add(t);
        }
        return list;
    }

    private <T> List<T> realExeQueryList(Class<?> clazz,PreparedStatement statement,List<FieldInfo> fieldInfoList) throws Exception {
        List<T> list = new ArrayList<T>();
        ResultSet set = statement.executeQuery();

        while (set.next()){
            T t = (T) clazz.getConstructor(new Class[] {}).newInstance();
            for (FieldInfo fieldInfo : fieldInfoList){
                Method setMethod = clazz.getMethod(fieldInfo.getSetMethodName(),new Class[] {FieldTypeEnum.getType(fieldInfo.getColType())});
                setMethod.invoke(t, new Object[] { set.getObject(fieldInfo.getColName())});// 调用对象的setXXX方法
            }
            list.add(t);
        }
        return list;
    }

    @Override
    public int exeUpdate(Object o, List list) throws Exception {

        String tableName = BeanMapInfo.getTableMap().get(o.getClass());
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+o.getClass().getName());
        }

        StringBuffer buffer = new StringBuffer("UPDATE ");
        buffer.append(tableName);
        buffer.append(" SET ");
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            buffer.append(kvPair.getKey()+"=?,");
        }

        String sql = buffer.substring(0,buffer.length() - 1);
        logger.info("selectSql : "+sql);

        PreparedStatement statement = this.createPreparedStatement(sql);

        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            convertParamValue(kvPair.getValue(),(i + 1),statement);
        }

        int x = realExeUpdateSql(null,statement);
        JdbcUtils.closeStatement(statement);
        return x;
    }

    @Override
    public int exeUpdate(Class clazz, List list, KVPair idPair) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }
        if(Objects.isNull(idPair)){
            throw  new NullPointerException("entity idPair is null.....");
        }

        StringBuffer buffer = new StringBuffer("UPDATE ");
        buffer.append(tableName);
        buffer.append(" SET ");
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            buffer.append(kvPair.getKey()+"=?,");
        }


        String sql = buffer.substring(0,buffer.length() - 1)+" WHERE "+idPair.getKey()+"=?";

        logger.info("selectSql : "+sql);

        PreparedStatement statement = this.createPreparedStatement(sql);

        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            convertParamValue(kvPair.getValue(),(i + 1),statement);
        }

        convertParamValue(idPair.getValue(),list.size() + 1,statement);

        int x = realExeUpdateSql(null,statement);
        JdbcUtils.closeStatement(statement);
        return x;
    }



    @Override
    public int exeDelete(Class clazz, Object id) throws Exception {
        StringBuffer buffer = new StringBuffer("DELETE  FROM  ");
        String  tName = BeanMapInfo.getTableMap().get(clazz);
        buffer.append(tName);
        buffer.append("  WHERE id = ?");
        logger.info("delete sql : "+buffer.toString());
        PreparedStatement statement = this.createPreparedStatement(buffer.toString());
        statement.setObject(1,id);

        int x= realExeUpdateSql(null,statement);
        JdbcUtils.closeStatement(statement);
        return x;
    }




    private int realExeUpdateSql(List<FieldInfo> fieldInfoList, PreparedStatement statement) throws SQLException {
        if(fieldInfoList != null ){
            int index = 1;
            for (FieldInfo fieldInfo : fieldInfoList){
                if(!Objects.isNull(fieldInfo.getFieldValue()) && fieldInfo.getIsID() == 0){
                    setPreParams(fieldInfo.getFieldValue(),fieldInfo.getColType(),index,statement);
                    index++;
                }
            }
        }
        return statement.executeUpdate();
    }

    /**
     * 设置参数
     * @param value
     * @param colType
     * @param index
     * @param ps
     * @throws SQLException
     */
    private void setPreParams(Object value,int colType,int index, PreparedStatement ps) throws SQLException {

        switch (colType){
            //int --- Integer
            case 3:
                ps.setInt(index,(Integer) value);
                break;
            case 4:
                ps.setInt(index,(Integer) value);
                break;

            //String
            case 5:
                ps.setString(index,(String) value);
                break;
            //long --- Long
            case 10:
                ps.setLong(index,(Long) value);
                break;
            case 11:
                ps.setLong(index,(Long) value);
                break;
            //Date
            case 14:
                ps.setObject(index, value);
                break;
            default:
                System.out.println("error......");

        }
    }


    private Object getResultSetValue(ResultSet rs,Field field) throws SQLException {

        String fieldName = field.getName();
        Object value = null;

        if(field.getType().equals(int.class) || field.getType().equals(Integer.class)){
            value = rs.getInt(fieldName);
        }
        else if(field.getType().equals(long.class) || field.getType().equals(Long.class)){
            value = rs.getLong(fieldName);
        }
        else if(field.getType().equals(String.class)){
            value = rs.getString(fieldName);
        }
        else if(field.getType().equals(Date.class)){
            value = rs.getDate(fieldName);
        }
        return value;
    }


    private Object getResultSetValue(ResultSet rs,int colType,String fieldName) throws SQLException {


        Object value = null;


        switch (colType){
            //int --- Integer
            case 3:
                value = rs.getInt(fieldName);
                break;
            case 4:
                value = rs.getInt(fieldName);
                break;

            //String
            case 5:
                value = rs.getString(fieldName);
                break;
            //long --- Long
            case 10:
                value = rs.getLong(fieldName);
                break;
            case 11:
                value = rs.getLong(fieldName);
                break;
            //Date
            case 14:
                value = rs.getDate(fieldName);
                break;
            default:
                System.out.println("error......");

        }
        return value;
    }


    private void convertParamValue(Object value,int index,PreparedStatement ps ) throws SQLException {
        if(value instanceof String){
            ps.setString(index,(String) value);
        }

        else if(value instanceof Date){
            ps.setObject(index,value);
        }

        else if(value instanceof Long){
            ps.setLong(index,(Long) value);
        }

        else if(value instanceof Integer){
            ps.setInt(index,(Integer) value);
        }
    }


    /**
     * 开启事务
     * @throws java.sql.SQLException
     */
    public void beginTransaction(int level) throws SQLException {
        Connection connection = hikariDataSource.getConnection();
        connection.setAutoCommit(false);
        connection.setTransactionIsolation(level);
        threadLocal.set(connection);
    }

    /**
     * 关闭事务并归还连接到连接池
     * @throws java.sql.SQLException
     */
    public void commitTransaction() throws SQLException {
        Connection connection = threadLocal.get();
        connection.commit();
        if(!threadLocal.get().getAutoCommit()){
            connection.setAutoCommit(true);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
        threadLocal.set(null);
    }

    /**
     * 关闭事务并归还连接到连接池
     * @throws java.sql.SQLException
     */
    public void rollbackTransaction() throws SQLException {
        Connection connection = threadLocal.get();
        connection.rollback();//恢复默认
        connection.setAutoCommit(true);
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        threadLocal.set(null);
    }


}
